﻿using System;
using System.Collections.Generic;
using System.Text;

using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;

using System.Drawing.Imaging;
using System.IO;

using System.Xml;
using System.Windows.Forms;
using System.Runtime.InteropServices;






namespace DBBase
{
    public class Query
    {

        public static string sDataBaseIp;
        public static string sDataBaseName;
        public static string sDataBaseUserName;
        public static string sDataBasePwd;
        public void readxml()
        {
            
                string path = "";
                path = System.Windows.Forms.Application.StartupPath + path + "\\DB.xml";
                string path1 = string.Format(@"{0}", path);

                XmlDocument xmlDoc = new XmlDocument();
                xmlDoc.Load(path1);
                XmlNode xn = xmlDoc.SelectSingleNode("DB1");
                XmlNodeList xnl = xn.ChildNodes;
                foreach (XmlNode xnf in xnl)
                {
                    XmlElement xe = (XmlElement)xnf;

                    sDataBaseIp = xe.GetAttribute("IP1");
                    sDataBaseName = xe.GetAttribute("mingcheng1");
                    sDataBaseUserName = xe.GetAttribute("yonghu1");
                    sDataBasePwd = xe.GetAttribute("mima1");
                }
                
           
        }
        public bool InsertDBAJLW(string sql)
        {
            readxml();
            string connectstr = string.Format("Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3};", sDataBaseIp, "AJLW", sDataBaseUserName, sDataBasePwd);
            SqlConnection sqlcnn = new SqlConnection(connectstr);
            //try
            {
                //打开数据库
                sqlcnn.Open();
                SqlCommand cmd = new SqlCommand(sql, sqlcnn);
                //执行插入操作，并返回受影响的行数
                int i = cmd.ExecuteNonQuery();
                //如果受影响的行数小于一则说明操作失败了
                if (i < 1)
                    return false;
                //关闭数据库
                sqlcnn.Close();
            }
            //catch (Exception er)
            //{
            //    string temp = er.Message.ToString();
            //    MessageBox.Show(temp);
            //    return false;
            //}
            return true;
        }
        public bool InsertDB(string sql)
        {
            readxml();
            string connectstr = string.Format("Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3};", sDataBaseIp, sDataBaseName, sDataBaseUserName, sDataBasePwd);
            SqlConnection sqlcnn = new SqlConnection(connectstr);
            //try
            {
                //打开数据库
                sqlcnn.Open();
                SqlCommand cmd = new SqlCommand(sql, sqlcnn);
                //执行插入操作，并返回受影响的行数
                int i = cmd.ExecuteNonQuery();
                //如果受影响的行数小于一则说明操作失败了
                if (i < 1)
                    return false;
                //关闭数据库
                sqlcnn.Close();
            }
            //catch (Exception er)
            //{
            //    string temp = er.Message.ToString();
            //    MessageBox.Show(temp);
            //    return false;
            //}
            return true;
        }
        public int selectDBcount(string sql)//查询该SQL语句的返回结果
        {
            readxml();
            int icount = 0;

            //try
            {
                string connectionstring = string.Format("Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3};", sDataBaseIp, sDataBaseName, sDataBaseUserName, sDataBasePwd);
                SqlConnection cnn = new SqlConnection(connectionstring);
                cnn.Open();
                SqlCommand cmd = new SqlCommand(sql, cnn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                DataTable dt = ds.Tables[0];//结果在这个table里，把它作为数据源就好了
                cnn.Close();
                icount = ds.Tables[0].Rows.Count;
                return icount;
            }
            //catch (Exception ex2)
            //{
            //    MessageBox.Show(ex2.ToString());
            //    return 0;
            //}

        }
        public string GetDBString(string sql, string DBname)
        {
            readxml();
            string m_stest;
            m_stest = "";
            //try
            {
                string connectionstring = string.Format("Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3};", sDataBaseIp, sDataBaseName, sDataBaseUserName, sDataBasePwd);
                SqlConnection cnn = new SqlConnection(connectionstring);
                cnn.Open();
                SqlCommand cmd = new SqlCommand(sql, cnn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                System.Data.DataTable dt = ds.Tables[0];//结果在这个table里，把它作为数据源就好了
                foreach (DataRow myDataRow in dt.Rows)
                {

                    m_stest = myDataRow[DBname].ToString().Trim();
                }
                cnn.Close();

            }
            //catch (Exception ex2)
            //{
            //    MessageBox.Show(ex2.ToString());
            //    return m_stest;

            //}
            return m_stest;
        }
        public int GetDBInt(string sql, string DBname)
        {
            readxml();
            //try
            {
                string connectionstring = string.Format("Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3};", sDataBaseIp, sDataBaseName, sDataBaseUserName, sDataBasePwd);
                SqlConnection cnn = new SqlConnection(connectionstring);
                cnn.Open();
                SqlCommand cmd = new SqlCommand(sql, cnn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                DataTable dt = ds.Tables[0];//结果在这个table里，把它作为数据源就好了
                foreach (DataRow myDataRow in dt.Rows)
                {

                    if (myDataRow[DBname].ToString().Trim() != "")
                    {
                        return int.Parse(myDataRow[DBname].ToString().Trim());
                    }
                    else
                    {
                        return 0;
                    }
                }
                cnn.Close();

            }
            //catch (Exception ex2)
            //{
            //    MessageBox.Show(ex2.ToString());
            //    return 0;

            //}
            return 0;
        }
        public DataTable GetTable(string sSQL)
        {
            readxml();
            //try
            {
                string connectionstring = string.Format("Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3};", sDataBaseIp, sDataBaseName, sDataBaseUserName, sDataBasePwd);
                SqlConnection sqlconn = new SqlConnection(connectionstring);
                sqlconn.Open();
                SqlCommand cmd = new SqlCommand(sSQL, sqlconn);
                cmd.CommandTimeout = 0;
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                sqlconn.Close();
                return ds.Tables[0];

            }
            //catch (Exception ex)
            //{
            //    MessageBox.Show(ex.Message);
            //    return new DataTable();
            //}
        }
        public DataTable GetTableAJLW(string sSQL)
        {
            readxml();
            //try
            {
                string connectionstring = string.Format("Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3};", sDataBaseIp, "AJLW", sDataBaseUserName, sDataBasePwd);
                SqlConnection sqlconn = new SqlConnection(connectionstring);
                sqlconn.Open();
                SqlCommand cmd = new SqlCommand(sSQL, sqlconn);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                sqlconn.Close();
                return ds.Tables[0];

            }
            //catch (Exception ex)
            //{
            //    MessageBox.Show(ex.Message);
            //    return new DataTable();
            //}
        }
        public DataTable GetTableM(string sSQL)
        {
            readxml();
            //try
            {
                string connectionstring = string.Format("Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3};", sDataBaseIp, "Managers", sDataBaseUserName, sDataBasePwd);
                SqlConnection sqlconn = new SqlConnection(connectionstring);
                sqlconn.Open();
                SqlCommand cmd = new SqlCommand(sSQL, sqlconn);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                sqlconn.Close();
                return ds.Tables[0];

            }
            //catch (Exception ex)
            //{
            //    MessageBox.Show(ex.Message);
            //    return new DataTable();
            //}
        }
        public bool UpdateTable(string sSQL)
        {
            readxml();
            //try
            {
            string connectionstring = string.Format("Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3};", sDataBaseIp, sDataBaseName, sDataBaseUserName, sDataBasePwd);
            SqlConnection sqlconn = new SqlConnection(connectionstring);
            sqlconn.Open();
            //string supdate=string.Format("update {0} set {1}={2} where {3}={4}"
            SqlCommand cmd = new SqlCommand(sSQL, sqlconn);
            //执行插入操作，并返回受影响的行数
            int i = cmd.ExecuteNonQuery();
            sqlconn.Close();
            if (i < 1)
                return false;
            else
            {
                return true;
            }
            }
            //catch (Exception ex)
            //{
            //    MessageBox.Show(ex.Message);
            //    return false;
            //}
        }

       
        public bool ExecuteS(string sSQL)
        {
            readxml();
           // try
            {
                string connectionstring = string.Format("Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3};", sDataBaseIp, sDataBaseName, sDataBaseUserName, sDataBasePwd);
                SqlConnection sqlconn = new SqlConnection(connectionstring);
                sqlconn.Open();
                SqlCommand cmd = new SqlCommand(sSQL, sqlconn);
                int i = Convert.ToInt32(cmd.ExecuteScalar());
                sqlconn.Close();
                if (i < 1)
                    return false;
                else
                {
                    return true;
                }
            }
            //catch (Exception ex)
            //{
            //    MessageBox.Show(ex.Message);
            //    return false;
            //}
        }
       


    }
}
